package com.imddy.spbatch001.config;

import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.datatype.jsr310.JavaTimeModule;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.ConnectionCallback;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ParameterizedPreparedStatementSetter;
import org.springframework.jdbc.core.SingleColumnRowMapper;
import org.springframework.util.StopWatch;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Random;
import java.util.concurrent.TimeUnit;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class DataSourceRepository {
    private static final Logger logger = LoggerFactory.getLogger(DataSourceRepository.class);
    private JdbcTemplate jdbcTemplate;

    public DataSourceRepository(JdbcTemplate pJdbcTemplate) {
        jdbcTemplate = pJdbcTemplate;
    }

    public <T> List<T> queryForList(String sql, Class<T> tClass){
        logger.info("sql: " + sql);
        List<T> resultList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(tClass));
        logger.info("resultList: " + resultList);
        return resultList;
    }

    public <T> List<T> queryForList(String sql, Class<T> tClass, Object... args){
        logger.info("sql: " + sql);
        List<T> resultList = jdbcTemplate.query(sql, args, new BeanPropertyRowMapper<>(tClass));
        logger.info("resultList: " + resultList);
        return resultList;
    }

    public <T> List<T> queryForListSingleColumn(String sql, Class<T> tClass){
        logger.info("sql: " + sql);
        List<T> resultList = jdbcTemplate.query(sql, new SingleColumnRowMapper<>(tClass));
        logger.info("resultList: " + resultList);
        return resultList;
    }

    public <T> List<T> queryForListSingleColumn(String sql, Class<T> tClass, Object... args){
        logger.info("sql: " + sql);
        List<T> resultList = jdbcTemplate.query(sql, args, new SingleColumnRowMapper<>(tClass));
        logger.info("resultList: " + resultList);
        return resultList;
    }

    public List<Map<String, Object>> queryForListMap(String sql){
        logger.info("sql: " + sql);
        List<Map<String, Object>> resultListMap = jdbcTemplate.queryForList(sql);
        logger.info("resultListMap: " + resultListMap);
        return resultListMap;
    }

    public List<Map<String, Object>> queryForListMap(String sql, Object... args){
        logger.info("sql: " + sql);
        List<Map<String, Object>> resultListMap = jdbcTemplate.queryForList(sql, args);
        logger.info("resultListMap: " + resultListMap);
        return resultListMap;
    }

    public Map<String, Object> queryForMap(String sql){
        logger.info("sql: " + sql);
        Map<String, Object> resultMap = jdbcTemplate.queryForMap(sql);
        logger.info("resultMap: " + resultMap);
        return resultMap;
    }

    public Map<String, Object> queryForMap(String sql, Object... args){
        logger.info("sql: " + sql);
        Map<String, Object> resultMap = jdbcTemplate.queryForMap(sql, args);
        logger.info("resultMap: " + resultMap);
        return resultMap;
    }


    public int update(String sql){
        logger.info("sql: " + sql);
        return jdbcTemplate.update(sql);
    }

    public int update(String sql, Object... args){
        logger.info("sql: " + sql);
        return jdbcTemplate.update(sql, args);
    }

    public int[] batchUpdate(String... sql){
        logger.info("sql: " + sql);
        return jdbcTemplate.batchUpdate(sql);
    }

    public int[] batchUpdate(String sql, List<Object[]> batchArgs){
        logger.info("sql: " + sql);
        return jdbcTemplate.batchUpdate(sql, batchArgs);
    }

    public int[] batchUpdate(String sql, BatchPreparedStatementSetter pss) {
        logger.info("sql: " + sql);
        return jdbcTemplate.batchUpdate(sql, pss);
    }

    public <T> int[][] batchUpdate(String sql, Collection<T> batchArgs, int batchSize, ParameterizedPreparedStatementSetter<T> pss) {
        logger.info("sql: " + sql);
        return jdbcTemplate.batchUpdate(sql, batchArgs, batchSize, pss);
    }







    public List<Map<String, Object>> executeSuperMultipleSql(String sqlText) {
        Random random = new Random();
        ObjectMapper objectMapper = new ObjectMapper().registerModule(new JavaTimeModule());
        List<String> sqlStatements = parseSqlStatements(sqlText);
        List<Map<String, Object>> results = new ArrayList<>();

        for (String sql : sqlStatements) {
            if (sql.trim().isEmpty()) {
                continue; // 跳过空语句
            }
            jdbcTemplate.execute(new ConnectionCallback<Void>() {
                @Override
                public Void doInConnection(Connection con) throws SQLException, DataAccessException {
                    con.beginRequest();
                    try (Statement stmt = con.createStatement()) {
                        logger.info("现在时间是: " + LocalDateTime.now().toString());
                        logger.info("sqlmm: " + sql);
                        StopWatch stopWatchmm = new StopWatch();
                        stopWatchmm.start();
                        boolean hasResults = stmt.execute(sql);
                        stopWatchmm.stop();
                        logger.info("执行sqlmm完成。");
                        logger.info("耗时：" + stopWatchmm.getTotalTimeSeconds() + "秒");
                        logger.info("现在时间是: " + LocalDateTime.now().toString());
                        Map<String, Object> result = new HashMap<>();
                        result.put("sql", sql);
                        if (hasResults) {
                            ResultSet rs = stmt.getResultSet();
                            result.put("result", extractResultSet(rs));
                        } else {
                            int updateCount = stmt.getUpdateCount();
                            result.put("result", updateCount);
                        }
                        results.add(result);
                        try {
                            logger.info("result: " + objectMapper.writeValueAsString(result));
                        } catch (JsonProcessingException e) {
                            throw new RuntimeException(e);
                        }
                    }
                    return null;
                }
            });
            try {
                int sleepSeconds = random.nextInt(5,10);
                logger.info("睡眠"+sleepSeconds+"秒钟...");
                TimeUnit.SECONDS.sleep(sleepSeconds);
            } catch (InterruptedException e) {
                throw new RuntimeException(e);
            }
        }
        return results;
    }


    public List<Map<String, Object>> executeMultipleSql(String sqlText) {
        Random random = new Random();
        ObjectMapper objectMapper = new ObjectMapper().registerModule(new JavaTimeModule());
        List<String> sqlStatements = parseSqlStatements(sqlText);
        List<Map<String, Object>> results = new ArrayList<>();

        jdbcTemplate.execute(new ConnectionCallback<Void>() {
            @Override
            public Void doInConnection(Connection con) throws SQLException, DataAccessException {
                try (Statement stmt = con.createStatement()) {
                    for (String sql : sqlStatements) {
                        if (sql.trim().isEmpty()) {
                            continue; // 跳过空语句
                        }
                        logger.info("现在时间是: " + LocalDateTime.now().toString());
                        logger.info("sqlmm: " + sql);
                        StopWatch stopWatchmm = new StopWatch();
                        stopWatchmm.start();
                        boolean hasResults = stmt.execute(sql);
                        stopWatchmm.stop();
                        logger.info("执行sqlmm完成。");
                        logger.info("耗时：" + stopWatchmm.getTotalTimeSeconds() + "秒");
                        logger.info("现在时间是: " + LocalDateTime.now().toString());
                        Map<String, Object> result = new HashMap<>();
                        result.put("sql", sql);
                        if (hasResults) {
                            ResultSet rs = stmt.getResultSet();
                            result.put("result", extractResultSet(rs));
                        } else {
                            int updateCount = stmt.getUpdateCount();
                            result.put("result", updateCount);
                        }
                        results.add(result);
                        try {
                            logger.info("result: " + objectMapper.writeValueAsString(result));
                        } catch (JsonProcessingException e) {
                            throw new RuntimeException(e);
                        }
                        try {
                            int sleepSeconds = random.nextInt(5,10);
                            logger.info("睡眠"+sleepSeconds+"秒钟...");
                            TimeUnit.SECONDS.sleep(sleepSeconds);
                        } catch (InterruptedException e) {
                            throw new RuntimeException(e);
                        }
                    }
                } catch (Exception e) {
                    //logger.error("错误SQL: " + sql);
                    logger.error("Error operation connection，执行SQL发生失败！", e);
                    throw new RuntimeException("Error executing SQL statements", e);
                }
                return null;
            }
        });
        return results;
    }

    private List<String> parseSqlStatements(String sqlText) {
        List<String> sqlStatements = new ArrayList<>();
        StringBuilder currentSql = new StringBuilder();
        boolean inComment = false;

        Pattern commentPattern = Pattern.compile("/\\*.*?\\*/|//.*?$", Pattern.DOTALL | Pattern.MULTILINE);
        Matcher matcher = commentPattern.matcher(sqlText);
        sqlText = matcher.replaceAll("");

        String[] lines = sqlText.split("\\r?\\n");

        for (String line : lines) {
            if (line.startsWith("--")) {
                continue; // 单行注释
            }
            if (line.contains("/*")) {
                inComment = true;
            }
            if (line.contains("*/")) {
                inComment = false;
                continue;
            }
            if (!inComment) {
                currentSql.append(line).append(" ");
                if (line.trim().endsWith(";")) {
                    sqlStatements.add(currentSql.toString().trim());
                    currentSql.setLength(0); // 清空StringBuilder
                }
            }
        }
        if (currentSql.length() > 0) {
            sqlStatements.add(currentSql.toString().trim());
        }
        return sqlStatements;
    }
    private Object extractResultSet(ResultSet rs) {
        List<Map<String, Object>> rows = new ArrayList<>();
        try {
            while (rs.next()) {
                Map<String, Object> row = new HashMap<>();
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    String columnName = rs.getMetaData().getColumnName(i);
                    Object columnValue = rs.getObject(i);
                    row.put(columnName, columnValue);
                }
                rows.add(row);
            }
        } catch (Exception e) {
            throw new RuntimeException("Error extracting result set", e);
        }
        return rows;
    }


}
